if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AddWindowsUserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AddWindowsUserLogin]
GO

PRINT 'Creating sp_AddWindowsUserLogin...'
GO

CREATE PROCEDURE dbo.sp_AddWindowsUserLogin
(
	@user_name sysname,
	@role_name sysname
)

AS

-- Try to remove the user first

-- First see if the user is in the master database
IF EXISTS (SELECT 1 FROM master.sys.syslogins WHERE [name] = @user_name)
-- They are in master.  Now see if the user is in the current database.
BEGIN
	IF EXISTS (SELECT 1 FROM sys.sysusers WHERE [name] = @user_name)
	BEGIN
		-- Remove the login from the current database
		EXEC sp_revokedbaccess @name_in_db = @user_name
	END
	-- Remove the login from the master database
	EXEC sp_revokelogin @loginame = @user_name
END

-- Now try to add the user to the master database

-- Check return code to see if it is 0 (success)
DECLARE @return_code int
EXEC @return_code = sp_grantlogin @loginame = @user_name
IF @return_code = 0
BEGIN
	-- The user was successfully added to the master database.  Now try to add the user to the 
	-- current database and assign to the specified role.
	EXEC @return_code = sp_grantdbaccess @loginame = @user_name, @name_in_db = @user_name
	IF @return_code = 0
	BEGIN
		EXEC sp_addrolemember @rolename = @role_name, @membername = @user_name

	END 
END
GO